﻿using System; 
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Transactions;
using System.Data.OracleClient;
using System.Text.RegularExpressions;
using System.Data.Common;
using System.Data.OleDb;
using System.Security.Cryptography;
using System.IO;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using LogManager;
namespace DBManager
{
    public  class DBWriter
    {
        public const string LogType = "DBManager";//日志类别（记录日志时使用。例： Logger.Write(LogType,msg）
        private static Dictionary<string, Database> m_DBArray = new Dictionary<string, Database>();
        private static string key = "judypass";
        private static string DB_TYPE_ORACLE = "ORACLE";
        private static string DB_TYPE_SQLSERVER = "SQLSERVER";
        private static string DB_TYPE_MYSQL = "MYSQL";
        private static string DB_TYPE_OLEDB = "OLEDB";
        private static Dictionary<string, string> m_DBTypeList = new Dictionary<string, string>();
        private static System.Text.RegularExpressions.Regex _providerRex = new System.Text.RegularExpressions.Regex("[Pp][Rr][Oo][Vv][Ii][Dd][Ee][Rr]=[^;]+;");

        #region//记录日志
        private static void WritLog(DBResult rs)
        {
            if ((rs != null) && (rs.bSucceedFlag == false) && (string.IsNullOrEmpty(rs.strMessage) == false))
            {
                Logger.Write(LogType, rs.strMessage);
            }
        }
        private static void WritLog(string strErrorMsg)
        {
            Logger.Write(LogType, strErrorMsg);
        }
        #endregion

        #region//获取连接字符串
        public static string GetConnectString(string strDBName)
        {
            string strConnstring = "";
            Database DBTmp = null;
            try
            {
                DBTmp = DatabaseFactory.CreateDatabase(strDBName);

                strConnstring = DBTmp.ConnectionString;
                try
                {
                    strConnstring = DESDecrypt(strConnstring);
                }
                catch
                {
                }
            }
            catch
            {

            }
            return strConnstring;
        }
        #endregion

        #region//解密连接字符串
        private static string DESDecrypt(string decryptString)
        {
            try
            {
                using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())
                {

                    byte[] inputByteArray = new byte[decryptString.Length / 2];
                    //反轉 
                    for (int x = 0; x < decryptString.Length / 2; x++)
                    {
                        int i = (Convert.ToInt32(decryptString.Substring(x * 2, 2), 16));
                        inputByteArray[x] = (byte)i;
                    }
                    //設定加密金鑰(轉為Byte) 
                    des.Key = ASCIIEncoding.ASCII.GetBytes(key);
                    //設定初始化向量(轉為Byte) 
                    des.IV = ASCIIEncoding.ASCII.GetBytes(key);
                    using (MemoryStream ms = new MemoryStream())
                    {
                        using (CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write))
                        {
                            //例外處理 
                            try
                            {
                                cs.Write(inputByteArray, 0, inputByteArray.Length);
                                cs.FlushFinalBlock();
                                //輸出資料 
                                return System.Text.Encoding.Default.GetString(ms.ToArray());
                            }
                            catch (CryptographicException)
                            {
                                //若金鑰或向量錯誤，傳回N/A 
                                return decryptString;
                            }
                        }
                    }
                }
            }
            catch
            {
                return decryptString;
            }
        }
        #endregion

        #region//创建数据库对象
        private static Database CreateDB(string strDBName)
        {
            if (string.IsNullOrEmpty(strDBName) == true)
                return null;
            if (m_DBArray.ContainsKey(strDBName) == true)
                return m_DBArray[strDBName];
            Database DBLocal = null, DBTmp = null;
            OracleDatabase DBOracle = null;
            MySqlDatabase DBMySQL = null;
            OledbDatabase DBOledb = null;
            string strErrorMessage = "";
            try
            {
                DBTmp = DatabaseFactory.CreateDatabase(strDBName);
                try
                {
                    string strConnstring = DBTmp.ConnectionString;
                    try
                    {
                        strConnstring = DESDecrypt(strConnstring);
                    }
                    catch
                    {
                    }

                    string noProviderStr = strConnstring;
                    if (_providerRex.IsMatch(strConnstring))
                    {
                        noProviderStr = strConnstring.Replace(_providerRex.Match(strConnstring).Value, "");
                    }
                    if (DBTmp.DbProviderFactory is System.Data.OleDb.OleDbFactory)
                    {
                        DBOledb = new OledbDatabase(strConnstring);
                    }
                    else if (DBTmp.DbProviderFactory is System.Data.OracleClient.OracleClientFactory)
                    {
                        DBOracle = new OracleDatabase(noProviderStr);
                    }
                    else if (DBTmp.DbProviderFactory is MySql.Data.MySqlClient.MySqlClientFactory)
                    {
                        DBMySQL = new MySqlDatabase(noProviderStr);
                    }
                    else
                        DBLocal = new SqlDatabase(noProviderStr);
                }
                catch (Exception ex)
                {
                    strErrorMessage = "创建数据库连接失败:" + ex.Message;
                }
            }
            catch (Exception ex)
            {
                strErrorMessage = "创建数据库连接失败:" + ex.Message;
            }
            if (string.IsNullOrEmpty(strErrorMessage) == false)
                WritLog(strErrorMessage);
            if (DBOledb != null)
            {
               // LogManager.Logger.Info("数据库对象创建成功（OLEDB）");
                try
                {
                    m_DBArray.Add(strDBName, DBOledb);
                    m_DBTypeList.Add(strDBName, DB_TYPE_OLEDB);
                }
                catch( Exception ex)
                {
                    LogManager.Logger.Fatal("数据库对象创建失败"+ strDBName +"error"+ ex.Message);
                }
                return DBOledb;
            }
            else if (DBOracle != null)
            {
               // LogManager.Logger.Info("数据库对象创建成功（Oracle）");
                try
                {
                    m_DBArray.Add(strDBName, DBOracle);
                    m_DBTypeList.Add(strDBName, DB_TYPE_ORACLE);
                }
                catch (Exception ex)
                {
                    LogManager.Logger.Fatal("数据库对象创建失败" + strDBName + "error" + ex.Message);
                }
                return DBOracle;
            }
            else if (DBMySQL != null)
            {
               // LogManager.Logger.Info("数据库对象创建成功（MySQL）");
                try
                {
                    m_DBArray.Add(strDBName, DBMySQL);
                    m_DBTypeList.Add(strDBName, DB_TYPE_MYSQL);
                }
                catch (Exception ex)
                {
                    LogManager.Logger.Fatal("数据库对象创建失败" + strDBName + "error" + ex.Message);
                }
                return DBMySQL;
            }
            else if (DBLocal != null)
            {
               // LogManager.Logger.Info("数据库对象创建成功（默认SQLServer）");
                try
                {
                    m_DBArray.Add(strDBName, DBLocal);
                    m_DBTypeList.Add(strDBName, DB_TYPE_SQLSERVER);
                }
                catch (Exception ex)
                {
                    LogManager.Logger.Fatal("数据库对象创建失败" + strDBName + "error" + ex.Message);
                }
                return DBLocal;
            }
            else
                return null;
        }

        #endregion
        #region//判断是否为oracle数据库
        public static bool IsOracleDatabase(string strDBName)
        {
            string strDBType = "";
            if (m_DBTypeList.ContainsKey(strDBName))
                strDBType = m_DBTypeList[strDBName];
            if (strDBType == DB_TYPE_ORACLE)
                return true;
            else
                return false;
        }
        #endregion

        #region//执行SQL语句
        public static DBResult ExecuteSQL(string strDBName, string strSQL)
        {
            return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL));
        }

        public static DBResult ExecuteSQL(string strDBName, string strSQL, object p1)
        {
            if (p1 is ArrayList)
                return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1 as ArrayList));
            else
                return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1));
        }

        public static DBResult ExecuteSQL(string strDBName, string strSQL, object p1, object p2)
        {
            return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2));
        }

        public static DBResult ExecuteSQL(string strDBName, string strSQL, object p1, object p2, object p3)
        {
            return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3));
        }

        public static DBResult ExecuteSQL(string strDBName, string strSQL, object p1, object p2, object p3, object p4)
        {
            return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4));
        }

        public static DBResult ExecuteSQL(string strDBName, string strSQL, object p1, object p2, object p3, object p4, object p5)
        {
            return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4, p5));
        }

        public static DBResult ExecuteSQL(string strDBName, string strSQL, object[] px)
        {
            return ExecuteSQL2(strDBName, SQLHelper.Extend(strDBName, strSQL, px));
        }

        public static DBResult ExecuteLongSQL(string strDBName, string strSQL,int timeout)
        {
            DBResult result = new DBResult();
            Database db = null; 
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                DbConnection conn =  db.CreateConnection();
                conn.Open();
                try
                {
                    DbCommand cmd = conn.CreateCommand();
                    cmd.CommandTimeout = timeout;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSQL;
                    cmd.ExecuteNonQuery();
                    result.bSucceedFlag = true;
                }
                catch (Exception ex)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
                }
                conn.Close();
            }
            catch (Exception ex2)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex2.Message + " (SQL：" + strSQL + ")";
            }
            WritLog(result);
            return result;
        }

        private static DBResult ExecuteSQL2(string strDBName, string strSQL)
        {
            int iCommandTimeout = 1800;
            try
            {
                string strCommandTimeout = ConfigurationManager.AppSettings["COMMAND_TIMEOUT"].ToString();
                iCommandTimeout = Convert.ToInt32(strCommandTimeout);
            }
            catch (Exception ex)
            {
                iCommandTimeout = 1800;
            }

            DBResult result = new DBResult();
            Database db = null;
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }

                DbConnection conn = db.CreateConnection();
                conn.Open();
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = iCommandTimeout;
                cmd.CommandType = CommandType.Text;
                if (IsOracleDatabase(strDBName))
                {
                    strSQL = "begin "+strSQL+" end;";
                }
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();

                conn.Close();

                //db.ExecuteNonQuery(CommandType.Text, strSQL);
                result.bSucceedFlag = true;
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
            }
            WritLog(result);
            return result;
        }

      
        #endregion

        #region//保存大字段
        public static DBResult ExecuteSQLForLargeData(string strDBName, string strSQL, byte[] data)
        {
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            //OracleDatabase oraDB = db as OracleDatabase;
            //if (oraDB == null)
                return ExecuteSQLForImage(strDBName, strSQL, data);
            //else
            //    return ExecuteSQLForBlob(strDBName, strSQL, data);
        }
        public static DBResult getDataTableForLargeData(string strDBName, string strSQL, byte[] data)
        {
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            //OracleDatabase oraDB = db as OracleDatabase;
            //if (oraDB == null)
            return getDataTableForImage(strDBName, strSQL, data);
            //else
            //    return ExecuteSQLForBlob(strDBName, strSQL, data);
        }
        public static DBResult ExecuteSQLForLargeData(string strDBName, string strSQL, ArrayList data)
        {
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            //OracleDatabase oraDB = db as OracleDatabase;
            //if (oraDB == null)
            return ExecuteSQLForImage(strDBName, strSQL, data);
            //else
            //    return ExecuteSQLForBlob(strDBName, strSQL, data);
        }

       

        private static DBResult ExecuteSQLForImage(string strDBName, string strSQL, byte[] data)
        {
            DBResult result = new DBResult();
            Database db = null;
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                System.Data.Common.DbCommand cmd = null;
                System.Data.Common.DbConnection conn = null;
                try
                {
                    conn = db.CreateConnection();
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandTimeout = 60;
                    cmd.CommandText = strSQL;
                    System.Data.Common.DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@1";
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.Binary;
                    param.Size = data.Length;
                    param.Value = data;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    result.bSucceedFlag = true;
                }
                catch(Exception e)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = e.Message;
                    WritLog(e.Message);
                }
                finally
                {
                    if (cmd != null)
                    {
                        if (cmd.Parameters != null && cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }
                        cmd.Dispose();
                    }
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
            }
            WritLog(result);
            return result;
        }

        private static DBResult getDataTableForImage(string strDBName, string strSQL, byte[] data)
        {
            DBResult result = new DBResult();
            Database db = null;
            DataSet ds = null;
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                System.Data.Common.DbCommand cmd = null;
                System.Data.Common.DbConnection conn = null;
                try
                {
                    conn = db.CreateConnection();
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandTimeout = 60;
                    cmd.CommandText = strSQL;
                    System.Data.Common.DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@1";
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.Binary;
                    param.Size = data.Length;
                    param.Value = data;
                    cmd.Parameters.Add(param);
                    //cmd.ExecuteNonQuery();
                    ds = db.ExecuteDataSet(cmd);
                    result.bSucceedFlag = true;
                }
                catch (Exception e)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = e.Message;
                    WritLog(e.Message);
                }
                finally
                {
                    if (cmd != null)
                    {
                        if (cmd.Parameters != null && cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }
                        cmd.Dispose();
                    }
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }
                    if ((ds == null) || (ds.Tables.Count == 0))
                    {
                        result.iRowCount = 0;
                    }
                    else
                    {
                        result.dtTable = ds.Tables[0];
                        result.iRowCount = ds.Tables[0].Rows.Count;
                    }
                }

            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
            }
            WritLog(result);
            return result;
        }
        private static DBResult ExecuteSQLForImage(string strDBName, string strSQL, ArrayList data)
        {
            DBResult result = new DBResult();
            Database db = null;
            try
            {
                db = CreateDB(strDBName);
                if (db == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                System.Data.Common.DbCommand cmd = null;
                System.Data.Common.DbConnection conn = null;
                try
                {
                    conn = db.CreateConnection();
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandText = strSQL;
                    for (int i = 1; i <= data.Count; i++)
                    {
                        byte[] bt = data[i-1] as byte[];
                        if (bt != null)
                        {
                            System.Data.Common.DbParameter param = cmd.CreateParameter();
                            param.ParameterName = "@"+i.ToString();
                            param.Direction = ParameterDirection.Input;
                            param.DbType = DbType.Binary;
                            param.Size = bt.Length;
                            param.Value = bt;
                            cmd.Parameters.Add(param);
                        }
                    }
                    cmd.ExecuteNonQuery();
                    result.bSucceedFlag = true;
                }
                catch (Exception e)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = e.Message;
                    WritLog(e.Message);
                }
                finally
                {
                    if (cmd != null)
                    {
                        if (cmd.Parameters != null && cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }
                        cmd.Dispose();
                    }
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行查询语句失败，原因：" + ex.Message + " (SQL：" + strSQL + ")";
            }
            WritLog(result);
            return result;
        }

        private static DBResult ExecuteSQLForBlob(string strDBName, string strSQL, byte[] data)
        {
            DBResult result = new DBResult();
            OracleDatabase oradb = null;
            string sql = SQLHelper.Extend(strDBName, strSQL);


            DataSet ds = new DataSet();
            OracleConnection conn = null;
            OracleCommand comm = null;
            try
            {
                oradb = CreateDB(strDBName) as OracleDatabase;
                if (oradb == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                conn = new OracleConnection(oradb.ConnectionString);
                conn.Open();
                comm = conn.CreateCommand();
                comm.CommandText = strSQL; 
                comm.Parameters.AddWithValue(":1", DBNull.Value);
                comm.Parameters[":1"].Value = data;
                comm.ExecuteNonQuery();
                result.bSucceedFlag = true;
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "oracle数据保存失败:" + ex.Message;
                ds = null;
            }
            finally
            {
                if (comm != null)
                {
                    if (comm.Parameters != null && comm.Parameters.Count > 0)
                    {
                        comm.Parameters.Clear();
                    }
                    comm.Dispose();
                }

                if ((conn != null) && (conn.State != ConnectionState.Closed))
                {
                    conn.Close();
                }
            }
            WritLog(result);
            return result;

        }
        public static DBResult ExecuteSQLForClob(string strDBName, string strSQL, string data)
        {
            DBResult result = new DBResult();
            OracleDatabase oradb = null;
            string sql = SQLHelper.Extend(strDBName, strSQL);


            DataSet ds = new DataSet();
            OracleConnection conn = null;
            OracleCommand comm = null;
            try
            {
                oradb = CreateDB(strDBName) as OracleDatabase;
                if (oradb == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                conn = new OracleConnection(oradb.ConnectionString);
                conn.Open();
                comm = conn.CreateCommand();
                comm.CommandText = strSQL;
                OracleParameter p1 = new OracleParameter("1", OracleType.Clob);
                p1.Value = data;
                comm.Parameters.Add(p1);
                comm.ExecuteNonQuery();
                result.bSucceedFlag = true;
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "oracle数据保存失败:" + ex.Message;
                ds = null;
            }
            finally
            {
                if (comm != null)
                {
                    if (comm.Parameters != null && comm.Parameters.Count > 0)
                    {
                        comm.Parameters.Clear();
                    }
                    comm.Dispose();
                }

                if ((conn != null) && (conn.State != ConnectionState.Closed))
                {
                    conn.Close();
                }
            }
            WritLog(result);
            return result;

        }
        private static DBResult ExecuteSQLForBlob(string strDBName, string strSQL, object[] px)
        {
            DBResult result = new DBResult();
            OracleDatabase oradb = null;
            string sql = SQLHelper.Extend(strDBName, strSQL);

            DataSet ds = new DataSet();
            OracleConnection conn = null;
            OracleCommand comm = null;
            try
            {
                oradb = CreateDB(strDBName) as OracleDatabase;
                if (oradb == null)
                {
                    result.bSucceedFlag = false;
                    result.strMessage = "创建数据库连接失败";
                    return result;
                }
                conn = new OracleConnection(oradb.ConnectionString);
                conn.Open();
                comm = conn.CreateCommand();
                comm.CommandText = strSQL;
                for (int i = 1; i <= px.Length; i++)
                {
                    byte[] data = px[i] as byte[];
                    if (data == null)
                        continue;
                    comm.Parameters.AddWithValue(":" + i.ToString(), DBNull.Value);
                    comm.Parameters[":" + i.ToString()].Value = data;
                }
                comm.ExecuteNonQuery();
                result.bSucceedFlag = true;
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "oracle数据保存失败:" + ex.Message;
                ds = null;
            }
            finally
            {
                if (comm != null)
                {
                    if (comm.Parameters != null && comm.Parameters.Count > 0)
                    {
                        comm.Parameters.Clear();
                    }
                    comm.Dispose();
                }
                if ((conn != null) && (conn.State != ConnectionState.Closed))
                {
                    conn.Close();
                }
            }
            WritLog(result);
            return result;

        }
        #endregion

        #region//执行存储过程
        public static DBResult ExecuteProcedure(string strDBName, string strProcessName)
        {
            DBResult result= null;
            try
            {
                System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter("null", 0);
                param.DbType = System.Data.DbType.Object;
                param.Direction = ParameterDirection.Output;
                System.Data.Common.DbParameter[] outPara = new System.Data.Common.DbParameter[] { param };
                result = ExecuteProcedure(strDBName, strProcessName, null, ref outPara);
            }
            catch (Exception ex)
            {
                result = new DBResult();
                result.bSucceedFlag = false;
                result.strMessage = ex.Message;
            }
            return result;
        }
        public static DBResult ExecuteProcedure(string strDBName, string strProcessName, DbParameter InParam)
        {
            DBResult result = null;
            try
            {
                DbParameter[] paramIn = new DbParameter[1] { InParam };

                System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter("null", 0);
                param.DbType = System.Data.DbType.Object;
                param.Direction = ParameterDirection.Output;
                DbParameter[] outPara = new DbParameter[] { param };
                result = ExecuteProcedure(strDBName, strProcessName, paramIn, ref outPara);
            }
            catch (Exception ex)
            {
                result = new DBResult();
                result.bSucceedFlag = false;
                result.strMessage = ex.Message;
            }
            return result;
        }

        public static DBResult ExecuteProcedureSQL(string strDBName, string strProcessName, object[] param)
        {
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            try
            {
                string strSQL = "", strParam="";
                for (int i = 0; i < param.Length; i++)
                {
                    if (i > 0)
                        strParam += ",";
                    strParam += "'"+param[i].ToString()+"'";
                }
                if(IsOracleDatabase(strDBName)==true)
                    strSQL = "Call " + strProcessName + "(" + strParam;
                else
                    strSQL = "exec "+strProcessName+" "+strParam;
                result = ExecuteSQL(strDBName, strSQL);
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = ex.Message;
            }
            return result;
        }

        public static DBResult ExecuteProcedure(string strDBName, string strProcessName, DbParameter[] InParam)
        {
            System.Data.OleDb.OleDbParameter poMsg = new System.Data.OleDb.OleDbParameter("msg", "");
            poMsg.DbType = System.Data.DbType.Object;
            poMsg.Size = 50;
            poMsg.Direction = ParameterDirection.Output;
            DbParameter[] OutParam = new OleDbParameter[1] { poMsg };
            return ExecuteProcedure(strDBName, strProcessName, InParam, ref OutParam);
        }

        public static DBResult ExecuteProcedure(string strDBName, string strProcessName, DbParameter[] InParam, ref DbParameter[] OutParam)
        {
            DBResult result = new DBResult();
            string strErrorNO = "";
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            try
            {
                DbConnection conn = null;
                DbCommand comm = null;
                int i = 0;
                try
                {
                    conn = db.CreateConnection();
                    conn.Open();
                    comm = conn.CreateCommand();
                    comm.CommandText = strProcessName;
                    comm.CommandType = CommandType.StoredProcedure;
                    if (InParam != null)
                    {
                        for (i = 0; i < InParam.Length; i++)
                        {
                            if (InParam[i].DbType == DbType.Object)
                                continue;
                            db.AddInParameter(comm, InParam[i].ParameterName, InParam[i].DbType, InParam[i].Value);
                        }
                    }
                    if (OutParam != null)
                    {
                        for (i = 0; i < OutParam.Length; i++)
                        {
                            if (OutParam[i].DbType == DbType.Object)
                                continue;
                            db.AddOutParameter(comm, OutParam[i].ParameterName, OutParam[i].DbType, OutParam[i].Size);

                        }
                    }
                    DataSet ds = db.ExecuteDataSet(comm);
                    if (OutParam != null)
                    {
                        for (i = 0; i < OutParam.Length; i++)
                        {
                            if (OutParam[i].DbType == DbType.Object)
                                continue;
                            OutParam[i].Value = comm.Parameters[i + InParam.Length].Value;
                        }
                    }
                    if ((ds != null) && (ds.Tables.Count > 0))
                    {
                        result.dtTable = ds.Tables[0];
                        result.iRowCount = result.dtTable.Rows.Count;
                    }
                    result.bSucceedFlag = true;
                }
                catch (Exception ex)
                {
                    strErrorNO = ex.Message;
                    result.bSucceedFlag = false;
                    result.strMessage = "存储过程" + strProcessName + "调用失败:" + ex.Message;
                }
                finally
                {
                    if (comm != null)
                    {
                        if (comm.Parameters != null && comm.Parameters.Count > 0)
                        {
                            comm.Parameters.Clear();
                        }
                        comm.Dispose();
                    }
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }

                    if (conn != null)
                    {
                        conn.Dispose();
                    }
                }

            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;

                result.strMessage = "执行存储过程" + strProcessName + "失败，原因：" + ex.Message;
            }
            if ((result.bSucceedFlag == false) && (strErrorNO.IndexOf("ORA-06550") >= 0))
                result = ExecuteProcedure2(strDBName, strProcessName, InParam, ref OutParam);
            return result;
        }

        private static DBResult ExecuteProcedure2(string strDBName, string strProcessName, DbParameter[] InParam)
        {
            System.Data.OleDb.OleDbParameter poMsg = new System.Data.OleDb.OleDbParameter("msg", "");
            poMsg.DbType = System.Data.DbType.Object;
            poMsg.Size = 50;
            poMsg.Direction = ParameterDirection.Output;
            DbParameter[] OutParam = new OleDbParameter[1] { poMsg };
            return ExecuteProcedure2(strDBName, strProcessName, InParam, ref OutParam);
        } 

        private static DBResult ExecuteProcedure2(string strDBName, string strProcessName, DbParameter[] InParam, ref DbParameter[] OutParam)
        {
            string strErrorNO = "";
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            try
            {
                DbConnection conn = null;
                DbCommand comm = null;
                int i = 0;
                try
                {
                    conn = db.CreateConnection();
                    conn.Open();
                    comm = conn.CreateCommand();
                    comm.CommandText = strProcessName;
                    comm.CommandType = CommandType.StoredProcedure;
                    if (InParam != null)
                    {
                        for (i = 0; i < InParam.Length; i++)
                        {
                            if (InParam[i].DbType == DbType.Object)
                                continue;
                            db.AddInParameter(comm, InParam[i].ParameterName, InParam[i].DbType, InParam[i].Value);
                        }
                    }
                    if (OutParam != null)
                    {
                        for (i = 0; i < OutParam.Length; i++)
                        {
                            if (OutParam[i].DbType == DbType.Object)
                                continue;
                            db.AddOutParameter(comm, OutParam[i].ParameterName, OutParam[i].DbType, OutParam[i].Size);

                        }
                    }
                    DataSet ds = null;
                    DbDataAdapter dp = db.GetDataAdapter();
                    dp.SelectCommand = comm;
                    ds = new DataSet();
                    dp.Fill(ds);
                    if (OutParam != null)
                    {
                        for (i = 0; i < OutParam.Length; i++)
                        {
                            if (OutParam[i].DbType == DbType.Object)
                                continue;
                            OutParam[i].Value = comm.Parameters[i + InParam.Length].Value;
                        }
                    }
                    if ((ds != null) && (ds.Tables.Count > 0))
                    {
                        result.dtTable = ds.Tables[0];
                        result.iRowCount = result.dtTable.Rows.Count;
                    }
                    result.bSucceedFlag = true;
                }
                catch (Exception ex)
                {
                    strErrorNO = ex.Message;
                    result.bSucceedFlag = false;
                    result.strMessage = "存储过程" + strProcessName + "调用失败:" + ex.Message;
                }
                finally
                {
                    if (comm != null)
                    {
                        if (comm.Parameters != null && comm.Parameters.Count > 0)
                        {
                            comm.Parameters.Clear();
                        }
                        comm.Dispose();
                    }
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }

                    if (conn != null)
                    {
                        conn.Dispose();
                    }
                }

            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "执行存储过程" + strProcessName + "失败，原因：" + ex.Message;
            }
            return result;
        }

        //#region//执行Oracle存储过程（可插入blob字段类型）
        //public static DBResult ExecuteNoQueryProcedureForOrcle(string strDBName, string strProcessName, DbParameter[] InParam)
        //{
        //    System.Data.OleDb.OleDbParameter poMsg = new System.Data.OleDb.OleDbParameter("msg", "");
        //    poMsg.DbType = System.Data.DbType.Object;
        //    poMsg.Size = 50;
        //    poMsg.Direction = ParameterDirection.Output;
        //    DbParameter[] OutParam = new OleDbParameter[1] { poMsg };
        //    return ExecuteNoQueryProcedureForOrcle(strDBName, strProcessName, InParam, ref OutParam);
        //}
        //public static DBResult ExecuteNoQueryProcedureForOrcle(string strDBName, string strProcessName, DbParameter[] InParam, ref DbParameter[] OutParam)
        //{
        //    OracleDatabase oradb = null;
        //    OracleConnection conn = null;
        //    OracleCommand comm = null;
        //    OracleCommand comm2 = null;
        //    OracleTransaction trans = null;
        //    OracleLob tempLob = null;
        //    DBResult result = new DBResult();
        //    int i = 0;
        //    try
        //    {
        //        oradb = CreateDB(strDBName) as OracleDatabase;
        //        if (oradb == null)
        //        {
        //            result.bSucceedFlag = false;
        //            result.strMessage = "创建数据库连接失败";
        //            return result;
        //        }
        //        conn = new OracleConnection(oradb.ConnectionString);
        //        conn.Open();
                
        //        comm = conn.CreateCommand();
        //        if (InParam != null)
        //        {
        //            for (i = 0; i < InParam.Length; i++)
        //            {
        //                if (InParam[i].DbType == DbType.Binary)
        //                {
        //                    byte[] tempBuff = InParam[i].Value as byte[];
        //                    trans = conn.BeginTransaction();
        //                    comm2 = conn.CreateCommand();
        //                    comm2.Transaction = trans;
        //                    comm2.Parameters.Clear();
        //                    comm2.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0);:tempblob := xx; end;";
        //                    comm2.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
        //                    comm2.ExecuteNonQuery();
        //                    tempLob = comm2.Parameters[0].Value as OracleLob;
        //                    tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
        //                    tempLob.Write(tempBuff, 0, tempBuff.Length);
        //                    tempLob.EndBatch();
        //                    trans.Commit();
        //                    trans = null;
        //                    tempLob.Position = 0;
        //                    OracleParameter pm = comm.Parameters.Add(InParam[i].ParameterName, OracleType.Blob);
        //                    pm.Value = tempLob;
        //                }
        //                else
        //                    comm.Parameters.AddWithValue(InParam[i].ParameterName, InParam[i].Value);
                        

        //            }
        //        }
        //        if (OutParam != null)
        //        {
        //            for (i = 0; i < OutParam.Length; i++)
        //            {
        //                if (OutParam[i].DbType == DbType.Object)
        //                    continue;
        //                oradb.AddOutParameter(comm, OutParam[i].ParameterName, OutParam[i].DbType, OutParam[i].Size);
        //            }
        //        }
        //        trans = conn.BeginTransaction();
        //        comm.Transaction = trans;
        //        comm.CommandText = strProcessName;
        //        comm.CommandType = CommandType.StoredProcedure;
        //        comm.ExecuteNonQuery();
        //        trans.Commit();
        //        if (OutParam != null)
        //        {
        //            for (i = 0; i < OutParam.Length; i++)
        //            {
        //                if (OutParam[i].DbType == DbType.Object)
        //                    continue;
        //                OutParam[i].Value = comm.Parameters[i + InParam.Length].Value;
        //            }
        //        }
        //        result.bSucceedFlag = true;
        //    }
        //    catch (Exception ex)
        //    {
        //        result.bSucceedFlag = false;
        //        result.strMessage = "数据保存失败:" + ex.Message;
        //    }
        //    finally
        //    {
        //        if (comm != null)
        //        {
        //            if (comm.Parameters != null && comm.Parameters.Count > 0)
        //            {
        //                comm.Parameters.Clear();
        //            }
        //            comm.Dispose();
        //        }

        //        if ((conn != null) && (conn.State != ConnectionState.Closed))
        //        {
        //            conn.Close();
        //        }
        //    }
        //    WritLog(result);
        //    return result;
        //}

        //private static OracleLob CreateTempLob(string strDBName, object buffer)
        //{
        //    OracleDatabase oradb = null;
        //    OracleConnection conn = null;
        //    OracleCommand comm = null;
        //    DBResult result = new DBResult();
        //    int i = 0;
        //    OracleLob tempLob = null;
        //    OracleTransaction trans = null;
        //    try
        //    {
        //        oradb = CreateDB(strDBName) as OracleDatabase;
        //        if (oradb == null)
        //        {
        //            return null;
        //        }
        //        conn = new OracleConnection(oradb.ConnectionString);
        //        conn.Open();
        //        trans = conn.BeginTransaction();
        //        comm = conn.CreateCommand();
        //        comm.Transaction = trans;
        //        byte[] tempBuff = buffer as byte[];
        //        comm.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0);:tempblob := xx; end;";
        //        comm.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
        //        comm.ExecuteNonQuery();
        //        tempLob = comm.Parameters[0].Value as OracleLob;
        //        tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
        //        tempLob.Write(tempBuff, 0, tempBuff.Length);
        //        tempLob.EndBatch();
        //        tempLob.Position = 0;
        //    }
        //    catch(Exception ex)
        //    {
        //        if (trans != null)
        //            trans.Rollback();
        //    }
        //    finally
        //    {
        //        //if (comm != null)
        //        //{
        //        //    if (comm.Parameters != null && comm.Parameters.Count > 0)
        //        //    {
        //        //        comm.Parameters.Clear();
        //        //    }
        //        //    comm.Dispose();
        //        //}

        //        if ((conn != null) && (conn.State != ConnectionState.Closed))
        //        {
        //            conn.Close();
        //        }
        //    }
        //    return tempLob;
        //}
        //#endregion
        #endregion

        #region//填充表
        public static DBResult FillTable(string strDBName, string strSQL, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL), ref dt);
        }

        public static DBResult FillTable(string strDBName, string strSQL, object p1, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1), ref dt);
        }

        public static DBResult FillTable(string strDBName, string strSQL, object p1, object p2, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2), ref dt);
        }

        public static DBResult FillTable(string strDBName, string strSQL, object p1, object p2, object p3, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3), ref dt);
        }

        public static DBResult FillTable(string strDBName, string strSQL, object p1, object p2, object p3, object p4, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4), ref dt);
        }

        public static DBResult FillTable(string strDBName, string strSQL, object p1, object p2, object p3, object p4, object p5, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, p1, p2, p3, p4, p5), ref dt);
        }

        public static DBResult FillTable(string strDBName, string strSQL, object[] px, ref DataTable dt)
        {
            return FillTable2(strDBName, SQLHelper.Extend(strDBName, strSQL, px), ref dt);
        }

        private static DBResult FillTable2(string strDBName, string strSQL, ref DataTable dt)
        {
            DBResult result = new DBResult();
            Database db = CreateDB(strDBName);
            if (db == null)
            {
                result.bSucceedFlag = false;
                result.strMessage = "创建数据库连接失败";
                return result;
            }
            try
            {
                DbDataAdapter adp = db.GetDataAdapter();
                adp.SelectCommand = db.GetSqlStringCommand(strSQL);
                adp.SelectCommand.Connection = db.CreateConnection();
                adp.Fill(dt);
                adp.SelectCommand.Connection.Close();
                result.bSucceedFlag = true;
            }
            catch (Exception ex)
            {
                result.bSucceedFlag = false;
                result.strMessage = "填充表信息失败，原因：" + ex.Message;
            }
            WritLog(result);
            return result;

        }
        #endregion

        #region//执行事务处理
        public static DBResult ExecuteTransaction(string strDBName, object[] strSQLArray)
        {
            DBResult result = new DBResult();
            string sql = "";
            if (IsOracleDatabase(strDBName) == true)
            {
                OracleDatabase oradb = null;
                OracleConnection conn = null;
                OracleCommand comm = null;
                OracleTransaction tranc = null;
                #region//oracle事物
                try
                {
                    oradb = CreateDB(strDBName) as OracleDatabase;
                    if (oradb == null)
                    {
                        result.bSucceedFlag = false;
                        result.strMessage = "创建数据库连接失败";
                        return result;
                    }

                    conn = new OracleConnection(oradb.ConnectionString);
                    conn.Open();
                    tranc = conn.BeginTransaction();
                    comm = conn.CreateCommand();
                    comm.Transaction = tranc;
                    for (int i = 0; i < strSQLArray.Length; i++)
                    {
                        sql = SQLHelper.Extend(strDBName, strSQLArray[i].ToString());
                        comm.CommandText = sql;
                        comm.ExecuteNonQuery();
                    }
                    tranc.Commit();
                    result.bSucceedFlag = true;
                    tranc = null;
                }
                catch (Exception ex)
                {
                    if (tranc != null)
                        tranc.Rollback();
                    result.bSucceedFlag = false;
                    result.strMessage = "数据保存失败，事物已回滚:" + ex.Message;
                }
                finally
                {
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }
                }
                #endregion
            }
            else
            {
                #region//sqlserver事物
                Database db = null;
                OleDbConnection conn = null;
                OleDbCommand comm = null;
                OleDbTransaction tranc = null;
                try
                {
                    db = CreateDB(strDBName);
                    if (db == null)
                    {
                        result.bSucceedFlag = false;
                        result.strMessage = "创建数据库连接失败";
                        return result;
                    }
                    if (db.ConnectionString.ToUpper().Contains("PROVIDER=") == false)
                    {
                        conn = new OleDbConnection(db.ConnectionString + ";Provider=SQLOLEDB;");
                      
                    }
                    else
                        conn = new OleDbConnection(db.ConnectionString);
                    conn.Open();
                    tranc = conn.BeginTransaction();
                    comm = conn.CreateCommand();
                    comm.Transaction = tranc;
                    for (int i = 0; i < strSQLArray.Length; i++)
                    {
                        sql = strSQLArray[i].ToString();
                        comm.CommandText = sql;
                        comm.ExecuteNonQuery();
                    }
                    tranc.Commit();
                    tranc = null;
                    result.bSucceedFlag = true;
                }
                catch (Exception ex)
                {
                    if (tranc != null)
                        tranc.Rollback();
                    result.bSucceedFlag = false;
                    result.strMessage = "数据保存失败，事物已回滚:" + ex.Message + "("+sql+")";
                }
                finally
                {
                    if ((conn != null) && (conn.State != ConnectionState.Closed))
                    {
                        conn.Close();
                    }
                }
                #endregion
            }
            WritLog(result);
            return result;
            //DBResult result = new DBResult();
            //Database db = CreateDB(strDBName);
            //if (db == null)
            //{
            //    result.bSucceedFlag = false;
            //    result.strMessage = "创建数据库连接失败";
            //    return result;
            //}
            //try
            //{
            //    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
            //    {
            //        foreach (string strSQL in strSQLArray)
            //        {
            //            db.ExecuteNonQuery(CommandType.Text, strSQL);
            //        }
            //        scope.Complete();
            //    }
            //    result.bSucceedFlag = true;
            //}
            //catch (Exception ex)
            //{
            //    result.strMessage = "执行事务处理失败，事务已经回退。错误原因：" + ex.Message;
            //    result.bSucceedFlag = false;
            //}
            //return result;
        }
        #endregion


    }
}

